﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ToolsLayer;
using Webcar.BLL;
using System.Data;

namespace Webcar.Car
{
    public partial class insure_statics : PageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        void BindData()
        {
            string sql = @"select dept_name,SUM(insure_money) month_all,
                         sum(case substring(a.start_date,1,7) when '{1}-01' then isnull(a.insure_money,0) else 0 end) month_01,
                         sum(case substring(a.start_date,1,7) when '{1}-02' then isnull(a.insure_money,0) else 0 end) month_02,
                         sum(case substring(a.start_date,1,7) when '{1}-03' then isnull(a.insure_money,0) else 0 end) month_03,
                         sum(case substring(a.start_date,1,7) when '{1}-04' then isnull(a.insure_money,0) else 0 end) month_04,
                         sum(case substring(a.start_date,1,7) when '{1}-05' then isnull(a.insure_money,0) else 0 end) month_05,
                         sum(case substring(a.start_date,1,7) when '{1}-06' then isnull(a.insure_money,0) else 0 end) month_06,
                         sum(case substring(a.start_date,1,7) when '{1}-07' then isnull(a.insure_money,0) else 0 end) month_07,
                         sum(case substring(a.start_date,1,7) when '{1}-08' then isnull(a.insure_money,0) else 0 end) month_08,
                         sum(case substring(a.start_date,1,7) when '{1}-09' then isnull(a.insure_money,0) else 0 end) month_09,
                         sum(case substring(a.start_date,1,7) when '{1}-10' then isnull(a.insure_money,0) else 0 end) month_10,
                         sum(case substring(a.start_date,1,7) when '{1}-11' then isnull(a.insure_money,0) else 0 end) month_11,
                         sum(case substring(a.start_date,1,7) when '{1}-12' then isnull(a.insure_money,0) else 0 end) month_12
                         from v_BS_INSURE a where a.dept_code in 
                        {0}
                        and substring(a.start_date,1,4)='{1}'
                        group by dept_name";
            DataSet ds = DB.ExecuteDataset(string.Format(sql, this.GetNonDeleteDeptSQL(), this.txtMonths.Text));
            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();
            this.GridView1.FooterRow.Cells[0].Text = "合计";
            for (int i = 1; i <= 12; i++)
            {
                this.GridView1.FooterRow.Cells[i].Text = ds.Tables[0].Compute("sum(month_" + (i).ToString().PadLeft(2, '0') + ")", "").ToString();
            }

            this.GridView1.FooterRow.Cells[13].Text = ds.Tables[0].Compute("sum(month_all)", "").ToString();
        }
        protected void btnQuery_Click(object sender, EventArgs e)
        {
            BindData();
        }
    }
}
